package Actions;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

public class gradeAppAction extends Action {
	
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		
		
		forms.showappInfoform lo = (forms.showappInfoform) form;
		
		int reviewcount = lo.getRvCount();
		String newcomment = lo.getComments();
		String reviewerUname = (String) request.getSession().getAttribute("uname");
		int newgrade =lo.getGrade();
		String username = lo.getUsername();
		int graderID = -1;
	
		//System.out.println(reviewerUname);	
		String url = "jdbc:postgresql://localhost:5432/Applications";
		Connection conn = null;
		Statement stmt = null;
		Statement stmt2 = null;
		PreparedStatement ps = null;
		String instruc = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
	
		
		try {
			// Registering Postgresql JDBC driver
			Class.forName("org.postgresql.Driver");
			// Open a connection to the database	
			conn = DriverManager.getConnection(url,"postgres","postgres");
			// Create the statement
	        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
	                    				ResultSet.CONCUR_UPDATABLE);
	        stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);			

	        
	        rs = stmt.executeQuery("SELECT revID FROM users WHERE user_name = '" 
					+ reviewerUname + "'");
	        while(rs.next()){	
	        	graderID = rs.getInt("revID");
	        }
	        
	        rs = stmt.executeQuery("SELECT * FROM Application WHERE app_id = "
	        					+"(SELECT distinct AppID FROM MainTable WHERE NameID = "
	        					+"(SELECT APP FROM users WHERE user_name ='"+username+"'))");
	        	
	        int appid = -1;
	        while(rs.next()){
	        	appid = rs.getInt("app_id");
	        }
	        
	        lo.setAppID(appid); 
	        
	        rs = stmt.executeQuery("SELECT * FROM Reviewers WHERE rev_id = '"+graderID+"'");
	
			while(rs.next()){
				int newappG = rs.getInt("appGraded")+1;
				int newappNG = rs.getInt("appNotGra")-1;
				
				instruc = "UPDATE Reviewers SET appGraded = '"+ 
				newappG+"' WHERE rev_id ='"+graderID+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();

				instruc = "UPDATE Reviewers SET appNotGra = '"+ 
				newappNG+"' WHERE rev_id ='"+graderID+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();				
			}
			
			
			
			if(reviewcount ==0){
				
				instruc = "UPDATE Application SET Grade1 = '"+ newgrade
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();
				
				instruc = "UPDATE Application SET Comment1 = '"+ newcomment
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();
				
				instruc = "UPDATE Application SET Grader1 = '"+ graderID
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();	
				
		        rs2 = stmt2.executeQuery("SELECT * FROM Reviewers WHERE rev_id != "
						+"(SELECT Grader1 FROM Application WHERE app_id = '"
						+appid+"')");
		        
		        while(rs2.next()){
		        	lo.updateNewreviewers(new forms.reviewerform(rs2.getString("username"),rs2.getInt("rev_id")));
		        }
				
				return mapping.findForward("add");
			}
			else if(reviewcount == 1){
			
				instruc = "UPDATE Application SET Grade2 = '"+ newgrade
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();
				
				instruc = "UPDATE Application SET Comment2 = '"+ newcomment
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();

				instruc = "UPDATE Application SET Grader2 = '"+ graderID
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();	
				
		        rs2 = stmt2.executeQuery("SELECT * FROM Reviewers WHERE rev_id != "
						+"(SELECT Grader1 FROM Application WHERE app_id = '"
						+appid+"') AND rev_id != "
						+"(SELECT Grader2 FROM Application WHERE app_id = '"
						+appid+"')");
		        
		        while(rs2.next()){
		        	lo.updateNewreviewers(new forms.reviewerform(rs2.getString("username"),rs2.getInt("rev_id")));
		        }
				
				return mapping.findForward("add");
			}
			else{
				instruc = "UPDATE Application SET Grade3 = '"+ newgrade
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();
				
				instruc = "UPDATE Application SET Comment3 = '"+ newcomment
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();
				
				instruc = "UPDATE Application SET Grader3 = '"+ graderID
				+"' WHERE app_id ='"+appid+"'";
				ps = conn.prepareStatement( instruc );
				ps.executeUpdate();	
				
			}
			

		}catch (SQLException e) 
		  { 
			System.out.println(e);
		  } catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  
			
		    return mapping.findForward("decided");
	
		
	}
}
